package com.fuye.groovy
import groovy.sql.Sql
import org.springframework.context.ApplicationContext
import org.springframework.context.support.ClassPathXmlApplicationContext
import org.springframework.jdbc.core.JdbcTemplate

import javax.sql.DataSource
import java.sql.Connection
import java.sql.ResultSet
import java.sql.Statement
import java.sql.Timestamp
/**
 * User: yf-wangpeng
 * Date: 13-10-16
 * Time: 下午5:01
 */
class TestMySql {
    static  DataSource dataSource;
     static String sql = 'select * from product_match where id >? '

    public static void main(String[] args) {
        init();
        read()
    }

    static void init() {
        ApplicationContext ctx = new ClassPathXmlApplicationContext("spring-config.xml");
        dataSource = ctx.getBean("dataSource")
        JdbcTemplate
    }


    private static void insert2Million() {
        def sql = Sql.newInstance("jdbc:mysql://127.0.0.1:3306/test", "root",
                "root", "com.mysql.jdbc.Driver")
        for (int i : 1..2000000) {
            Random  r= new Random();
            int ri=r.nextInt(100000)
            int jdid = 100000 + i+ri;
            int oppid = 100000000 + i+r.nextInt(90092);

            def date = new Timestamp(new Date(r.nextLong(new Date().getTime())));
            int opid = new Random().nextInt(5) + 1
            sql.executeInsert("insert into product_match (jd_product_id,opp_product_id,created_time,opponent_id) values (${jdid},${oppid},${date},${opid})")

        }
        println "over.......!";
    }

    static void read(){
        Connection connection=dataSource.getConnection();
        connection = dataSource.getConnection();
//        Statement stmt = connection.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
        Statement stmt=connection.prepareStatement(sql, java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY)
        stmt.setObject(1,100222);
        stmt.setFetchSize(Integer.MIN_VALUE);
        ResultSet rs=stmt.executeQuery()
        //streaming laoding,will come occur wait_time out exception...
        int count = 0;
        while(rs.next()){
            count++;
            if(count%100==0)
             println "count->"+count+"rs->"+rs.getString(1) +"->"+rs.getString(2);
//                Thread.sleep(1000);
        }
    }

    static void read1() {
        Connection connection = dataSource.getConnection();
        connection = dataSource.getConnection();
        Statement stmt = connection.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
        connection.prepareStatement()
        stmt.setFetchSize(Integer.MIN_VALUE);
        ResultSet rs = stmt.executeQuery("select * from product_match where id = ?",1)
        //streaming laoding,will come occur wait_time out exception...
        int count = 0;
        while (rs.next()) {
            count++;
            if (count++ % 60 == 0) println count;
            Thread.currentThread().sleep(1000);
        }
        println "count->" + count
    }
}
